import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Hashtable;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerConfigurationException;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import org.apache.commons.configuration.ConfigurationException;
import org.apache.commons.configuration.PropertiesConfiguration;
import org.apache.log4j.Logger;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

import com.herohead.CDLogger;
import com.herohead.Misc;
import com.herohead.Rabbit_publish;
import com.herohead.Rabbitmq;
import com.rabbitmq.client.AMQP;
import com.rabbitmq.client.Channel;
import com.rabbitmq.client.GetResponse;
import com.rabbitmq.client.QueueingConsumer;


public class updateBackup_log {

	public static PreparedStatement check;
	public static PreparedStatement upsertStatement;
	public static Connection connect = null;
	public static Statement statement = null;
	public static String checkForRecord = null;
	public static int cc=0;
	public static int recAdded = 0;
	public static int dupe = 0;
	public static String xmlSourceSystem = null;

	//static PreparedStatement upsertStatement = null;
	//static PreparedStatement check = null;
	//static Connection connect = null;
	//static Statement statement = null;
	public static String sqlString = null;
	static ResultSet resultSet =null;


	private static org.apache.log4j.Logger log= Logger.getLogger(updateBackup_log.class);

	static Hashtable<String,Integer> recCountType = new Hashtable<String,Integer>();



	/**
	 * @param args
	 * @throws Exception 
	 * @throws ConfigurationException 
	 */
	public static void main(String[] args) throws Exception, ConfigurationException {
		// TODO Auto-generated method stub

		final CDLogger cdlog = new CDLogger();

		Runtime.getRuntime().addShutdownHook(new Thread() {
			public void run() { 
				cdlog.programStop(log);
			}
		});


		//CDLogger cdlog = new CDLogger();

		cdlog.programStart(log);

		String className = "updateBackup_log";
		PropertiesConfiguration config = new PropertiesConfiguration(className + ".config");
		log.debug("reading config file");

		String rabbitmqserver = config.getString("rabbitmqserver");
		String RMQQueue = config.getString("RMQQueue");
		String TotalsQueue = config.getString("TotalsQueue");
		String RMQVirtualHost= config.getString("RMQVirtualHost"); 
		String RMQUsername= config.getString("RMQUsername");
		String RMQPassword= config.getString("RMQPassword");
		String SQLConnection = config.getString("SQLConnection");

		/*
		 *  backupLogTable=backup.backup_log
		 */
		String backupLogTable = config.getString("backupLogTable");


		log.debug("Connection to RabbitMQ server");

		Rabbitmq rmq = new Rabbitmq();
		rmq.setHostname(rabbitmqserver);
		rmq.setLogin(RMQVirtualHost, RMQPassword, RMQUsername);
		//log.info("Connecting to " + rabbitmqserver);
		rmq.setupChannel();
		rmq.setQueue(RMQQueue);

		rmq.declareQueDurable();
		Channel channel = rmq.getChannel();
		//QueueingConsumer consumer = new QueueingConsumer(channel);

		//channel.basicConsume(rmq.getQueue(), true, consumer);

		sqlString = "insert into " + backupLogTable  +
				" (timestamp,system,sourceSystem,savegroupName,saveGroupStatus," +
				"saveGroupStartTime,saveGroupEndTime,saveGroupRestartTime," +
				"saveGroupClientCount,saveGroupClientList,"+
				"clientName,clientStatus,clientDetails,clientFileCount,clientBytesScanned,clientNewBytes,clientPerCentNew,started_ts,BackupType) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		checkForRecord = "select count(*) cnt from " + backupLogTable + " where clientName = ? and timestamp(started_ts) = ? and sourceSystem = ?";

		connect = DriverManager.getConnection(SQLConnection);

		statement = connect.createStatement();

		check = connect.prepareStatement(checkForRecord);

		channel = rmq.getChannel();

		log.debug("Waiting for message");
		//System.out.println("Waiting for message");
		String message = null;
		long deliveryTag;

		boolean autoAck = false;
		//GetResponse response = channel.basicGet(RMQQueue, autoAck);
		GetResponse response = null;
		@SuppressWarnings("unused")
		AMQP.BasicProperties props;
		do {
			response = channel.basicGet(RMQQueue, autoAck);
			if (response == null) {
				break;
			} else {
				cc++;
				props = response.getProps();
				// byte[] body = response.getBody();
				message = new String(response.getBody());
				deliveryTag = response.getEnvelope().getDeliveryTag();
				readXML(message);
				//channel.basicAck(response.getEnvelope().getDeliveryTag(), true);
				channel.basicAck(deliveryTag, false);
			}
		} while (true);

		close();


		AMQP.BasicProperties properties = new AMQP.BasicProperties();
		//properties.setDeliveryMode(2);
		boolean durable = true;
		Channel totals = rmq.setupChannel();
		//properties = new AMQP.BasicProperties();
		//properties.setDeliveryMode(2);
		totals.queueDeclare(TotalsQueue, durable, false, false, null);

		/* sourceSystem = sourceSystem
		 * type = publish
		 * publichedCount = count
		 */

		String xml = null;

		for (String key : recCountType.keySet()) {
			//System.out.println(key + ":" + recCountType.get(key));
			xml= formatTotalsXML(key.split(":")[0],Integer.toString(recCountType.get(key)),key.split(":")[1],Misc.getTimestamp());
			totals.basicPublish("", TotalsQueue, properties, xml.getBytes());
			log.info("Summary total for " + key.split(":")[0] + " type " + key.split(":")[1] + " count :"  + Integer.toString(recCountType.get(key)));
		}


		/*	


		if (recAdded > 0) {
			xml= formatTotalsXML(xmlSourceSystem,Integer.toString(recAdded),"Added",Misc.getTimestamp());
			totals.basicPublish("", "Totals_log", properties, xml.getBytes());
			log.info("Summary total for recAdded published count added " + recAdded);
		}
		if (dupe > 0) {
			xml= formatTotalsXML(xmlSourceSystem,Integer.toString(dupe),"dupe",Misc.getTimestamp());
			totals.basicPublish("", "Totals_log", properties, xml.getBytes());
			log.info("Summary total for dupe published count added " + dupe);
		}
		 */
		System.exit(0);
	}


	public static String formatTotalsXML(String sourceSystem,String count, String type, String timestamp ) throws TransformerException{

		String timeStamp = Misc.getTimestamp();
		try {
			DocumentBuilderFactory docFactory = DocumentBuilderFactory.newInstance();
			DocumentBuilder docBuilder = docFactory.newDocumentBuilder();

			// root elements
			Document doc = docBuilder.newDocument();
			Element rootElement = doc.createElement("totals_log");
			doc.appendChild(rootElement);
			Element e0 = doc.createElement("sourceSystem");
			e0.appendChild(doc.createTextNode(sourceSystem));
			rootElement.appendChild(e0);
			Element es = doc.createElement("count");
			es.appendChild(doc.createTextNode(count));
			rootElement.appendChild(es);
			Element es1 = doc.createElement("type");
			es1.appendChild(doc.createTextNode(type));
			rootElement.appendChild(es1);
			Element est = doc.createElement("timestamp");
			est.appendChild(doc.createTextNode(timestamp));
			rootElement.appendChild(est);

			TransformerFactory transformerFactory = TransformerFactory.newInstance();
			Transformer transformer = transformerFactory.newTransformer();

			DOMSource source = new DOMSource(doc);
			StreamResult result = new StreamResult(new StringWriter());
			transformer.transform(source, result);
			//System.out.println(result.getWriter().toString());

			return result.getWriter().toString();

		} catch (ParserConfigurationException pce) {
			pce.printStackTrace();
			return pce.toString();
		} catch (TransformerConfigurationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return e.toString();
		}
	}


	public static void readXML(String emailXml) throws Exception {

		String xmlTimeStamp = null;
		String xmlSystem = null;

		String xmlsaveGroupName = null;
		String xmlsavegroupStatus = null;
		String xmlClient = null;
		String xmlStatus = null;
		String xmlClientDetails = null;
		String xmlsaveGroupStartTime = null;
		String xmlsaveGroupEndTime = null;
		String xmlsaveGroupRestartTime = null;
		String xmlsaveGroupClientCount = null;
		String xmlsaveGroupClientList = null;
		String xmlClientFileCount=null;
		String xmlClientBytesScanned=null;
		String xmlClientNewBytes=null;
		String xmlClientPerCentNew=null;
		String xmlStarted_ts=null;
		String xmlgetBackupType=null;

		try {
			DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance();
			DocumentBuilder dBuilder = dbFactory.newDocumentBuilder();
			Document doc = dBuilder.parse(new ByteArrayInputStream(emailXml.getBytes("UTF-8")));
			doc.getDocumentElement().normalize();

			NodeList nList = doc.getElementsByTagName("savegrouplog");
			for (int temp = 0; temp < nList.getLength(); temp++) {
				Node nNode = nList.item(temp);
				if (nNode.getNodeType() == Node.ELEMENT_NODE) {
					Element eElement = (Element) nNode;
					xmlTimeStamp = checkNull(eElement.getElementsByTagName("timestamp").item(0).getTextContent(),true);
					xmlSystem = checkNull(eElement.getElementsByTagName("system").item(0).getTextContent(),true);
					xmlsaveGroupName = checkNull(eElement.getElementsByTagName("saveGroupName").item(0).getTextContent(),true);
					xmlsavegroupStatus = checkNull(eElement.getElementsByTagName("saveGroupStatus").item(0).getTextContent(),true);
					xmlsaveGroupStartTime = checkNull(eElement.getElementsByTagName("saveGroupStartTime").item(0).getTextContent(),true);
					xmlsaveGroupEndTime = checkNull(eElement.getElementsByTagName("saveGroupEndTime").item(0).getTextContent(),true);
					xmlsaveGroupRestartTime = checkNull(eElement.getElementsByTagName("saveGroupRestartTime").item(0).getTextContent(),true);
					xmlsaveGroupClientCount = checkNull(eElement.getElementsByTagName("saveGroupClientCount").item(0).getTextContent(),false);
					xmlsaveGroupClientList = checkNull(eElement.getElementsByTagName("saveGroupClientList").item(0).getTextContent(),true);
					xmlClient = checkNull(eElement.getElementsByTagName("clientName").item(0).getTextContent(),true);
					xmlStatus = checkNull(eElement.getElementsByTagName("clientStatus").item(0).getTextContent(),true);
					xmlClientDetails = checkNull(eElement.getElementsByTagName("clientDetails").item(0).getTextContent(),true);
					xmlClientFileCount = checkNull(eElement.getElementsByTagName("clientFileCount").item(0).getTextContent(),false);
					xmlClientBytesScanned = checkNull(eElement.getElementsByTagName("clientBytesScanned").item(0).getTextContent(),false);
					xmlClientNewBytes = checkNull(eElement.getElementsByTagName("clientNewBytes").item(0).getTextContent(),false);
					xmlClientPerCentNew = checkNull(eElement.getElementsByTagName("clientPerCentNew").item(0).getTextContent(),false);
					xmlSourceSystem = checkNull(eElement.getElementsByTagName("sourceSystem").item(0).getTextContent(),true);
					xmlStarted_ts = checkNull(eElement.getElementsByTagName("started_ts").item(0).getTextContent(),true);
					xmlgetBackupType = checkNull(eElement.getElementsByTagName("backupType").item(0).getTextContent(),true);
				}
				dBuilder.reset();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		Misc m = new Misc();
		upSertLog( xmlTimeStamp, xmlSystem, xmlSourceSystem, xmlsaveGroupName,  xmlsavegroupStatus,  xmlsaveGroupStartTime,  xmlsaveGroupEndTime,  xmlsaveGroupRestartTime,  xmlsaveGroupClientCount,  xmlsaveGroupClientList,  xmlClient,  xmlStatus,  m.trimString(xmlClientDetails,4000),  m.bytesToKB(xmlClientFileCount),  xmlClientBytesScanned,  m.bytesToMB(xmlClientNewBytes),  xmlClientPerCentNew,xmlStarted_ts,xmlgetBackupType);
		m = null;
	}


	public static void upSertLog(String timestamp,String system,String sourceSystem,String savegroupName, String saveGroupStatus, String saveGroupStartTime, String saveGroupEndTime, String saveGroupRestartTime, String saveGroupClientCount, String saveGroupClientList, String clientName, String clientStatus, String clientDetails, String clientFileCount, String clientBytesScanned, String clientNewBytes, String clientPerCentNew,String started_ts,String backupType) throws SQLException, Exception {


		/*	
		PreparedStatement upsertStatement = null;
		PreparedStatement check = null;
		Connection connect = null;
		Statement statement = null;

				connect = DriverManager.getConnection("jdbc:mysql://us01s-netops01.am.mt.mtnet?user=backup&password=networker");
				statement = connect.createStatement();

		sqlString = "insert into backup.backup_log " +
				"(timestamp,system,sourceSystem,savegroupName,saveGroupStatus," +
				"saveGroupStartTime,saveGroupEndTime,saveGroupRestartTime," +
				"saveGroupClientCount,saveGroupClientList,"+
				"clientName,clientStatus,clientDetails,clientFileCount,clientBytesScanned,clientNewBytes,clientPerCentNew) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		 */
		//String checkForRecord = "select count(*) cnt from backup.backup_log where clientName = ? and saveGroupStartTime = ? and sourceSystem = ?";
		//check = connect.prepareStatement(checkForRecord);

		check.setString(1, clientName);

		/*
				if (saveGroupStartTime.indexOf(".") != -1) {
					check.setString(2, saveGroupStartTime.substring(0,saveGroupStartTime.indexOf(".")));
				} else {
					check.setString(2, saveGroupStartTime);
				}
		 */
		check.setString(2, started_ts);

		check.setString(3, sourceSystem);
		resultSet = check.executeQuery();
		String recCnt = null;
		while (resultSet.next()) {
			recCnt = resultSet.getString("cnt");
		}
		// if record not already in table add it.
		if (recCnt.equals("0")) {
			upsertStatement = connect.prepareStatement(sqlString);
			upsertStatement.setString(1, timestamp);
			upsertStatement.setString(2, system);
			upsertStatement.setString(3, sourceSystem);
			upsertStatement.setString(4, savegroupName);
			upsertStatement.setString(5, saveGroupStatus);
			upsertStatement.setString(6, saveGroupStartTime);
			upsertStatement.setString(7, saveGroupEndTime);
			upsertStatement.setString(8, saveGroupRestartTime);
			upsertStatement.setString(9, saveGroupClientCount);
			upsertStatement.setString(10, saveGroupClientList);
			upsertStatement.setString(11, clientName);
			upsertStatement.setString(12, clientStatus);
			upsertStatement.setString(13, clientDetails);
			upsertStatement.setString(14, clientFileCount);
			upsertStatement.setString(15, clientBytesScanned);
			upsertStatement.setString(16, clientNewBytes);
			upsertStatement.setString(17, clientPerCentNew);
			system = system.toUpperCase();
			if (system.equals("NETWORKER")) {
				//public static PreparedStatement check;
				PreparedStatement seq = connect.prepareStatement("select backup.seq('networker') as seq");
				//ResultSet resultSet1 =null;
				ResultSet resultSet1 = seq.executeQuery();
				resultSet1.next();
				String seqNum = resultSet1.getString("seq");
				upsertStatement.setString(18, seqNum + ".000");
				upsertStatement.setString(19, backupType);
				resultSet1.close();
				seq.close();
				
			} else {
				upsertStatement.setString(18, stripTime(started_ts));
				upsertStatement.setString(19, "Full");
			}
			
			
			
			
			//System.out.println(upsertStatement.getMetaData());
			log.debug(cc + " Adding to table : " + clientName + " " +  saveGroupStartTime);
			addToCount(recCountType,sourceSystem + ":" + "added");
			upsertStatement.execute();

		} else {
			log.debug(cc + " Dup record found not updating : " + clientName + " " + saveGroupStartTime + " " + sourceSystem);
			addToCount(recCountType,sourceSystem + ":" + "dupe");
		}
		//check.close();
		resultSet.close();

	}

	public static String checkNull(String s,boolean str) {
		if (s.length() == 0) 
			if (str) s = null;
			else s = "0";
		return s;
	}

	private static void close() {
		try {
			if (check != null) {
				check.close();
			}
			if (resultSet != null) {
				resultSet.close();
			}

			if (upsertStatement != null) {
				upsertStatement.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (connect != null) {
				connect.close();
			}
		} catch (Exception e) {

		}
	}

	public static String stripTime(String st) {

		String s1 = st.replace("-", "");
		s1 = s1.replace(" ", "");
		s1 = s1.replace(":", "");

		return s1;
	}

	public static int addToCount(Hashtable<String,Integer> myMap,String type) {

		int t = 0;

		if (myMap.containsKey(type)) {
			t = myMap.get(type) +1;
		} else {
			t =1;
		}

		myMap.put(type, t);

		return t;
	}
	
}


